package com.example.service;

import com.example.util.BaseRepository;
import com.example.util.QueryParams;
import org.hibernate.transform.Transformers;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class OrderService extends BaseRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public Map<String, Object> getOrderRecord(QueryParams queryParams, Pageable pageable) {
        Map<String, Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("SELECT t1.`contacts_tel`, t1.`pers_name`,date_format(t1.create_time, '%Y-%m-%d %H:%i:%s') as create_time, t1.`order_flag`, t1.`borrowing_amout`, t1.`expect_rate`, t1.`loan_period`, date_format(t1.actual_lend_time, '%Y-%m-%d %H:%i:%s') as actual_lend_time, ");
        sql.append("t1.`actual_rate` , t1.`actual_lend_value`, t1.`actual_loan_period` FROM de_declaration t1, bs_user t2 WHERE t1.`contacts_tel` = t2.`user_name` ");
        List values = analysisQueryParams(sql, queryParams);
        Query queryData = entityManager.createNativeQuery(sql.toString());
        for (int i = 0; i < values.size(); i++) {
            queryData.setParameter(i + 1, values.get(i));
        }
        map.put("total", queryData.getResultList().size());
        //设置分页参数
        queryData.setFirstResult(pageable.getOffset());
        queryData.setMaxResults(pageable.getPageSize());
        //转换为Map集合
        queryData.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        map.put("data", queryData.getResultList());
        return map;
    }

    public Map<String, Object> assessmentOfReal(QueryParams queryParams, Pageable pageable) {
        Map<String, Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("SELECT t1.`user_name`,t1.`nick_name`,date_format(t2.`input_time`, '%Y-%m-%d %H:%i:%s') as input_time,t2.`filter`,t2.`area`,t2.`totalprice`,t2.`audit_flag` FROM bs_user t1,bs_card_info t2 WHERE t2.`tel_number` = t1.`user_name`");
        List values = analysisQueryParams(sql, queryParams);
        Query queryData = entityManager.createNativeQuery(sql.toString());
        for (int i = 0; i < values.size(); i++) {
            queryData.setParameter(i + 1, values.get(i));
        }
        map.put("total", queryData.getResultList().size());
        //设置分页参数
        queryData.setFirstResult(pageable.getOffset());
        queryData.setMaxResults(pageable.getPageSize());
        //转换为Map集合
        queryData.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        map.put("data", queryData.getResultList());
        return map;
    }

    public Map<String,Object> commissionInfo(QueryParams queryParams, Pageable pageable) {
        Map<String, Object> map = new HashMap<>();
        StringBuilder sql = new StringBuilder("SELECT reward_uid, reward_unm, reward_type, decl_uid, decl_unm, borrowing_amout, expect_rate, actual_lend_value, DATE_FORMAT( actual_lend_time_sys, '%Y-%m-%d %H:%i:%s' ) AS actual_lend_time_sys, DATE_FORMAT( actual_lend_time, '%Y-%m-%d %H:%i:%s' ) AS actual_lend_time, reward_money, reward_money_real, reward_money_type FROM de_reward WHERE 1=1");
        List values = analysisQueryParams(sql, queryParams);
        Query queryData = entityManager.createNativeQuery(sql.toString());
        for (int i = 0; i < values.size(); i++) {
            queryData.setParameter(i + 1, values.get(i));
        }
        map.put("total", queryData.getResultList().size());
        //设置分页参数
        queryData.setFirstResult(pageable.getOffset());
        queryData.setMaxResults(pageable.getPageSize());
        //转换为Map集合
        queryData.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        map.put("data", queryData.getResultList());
        return map;
    }

    public Map<String,Object> commissionSummary(Integer partnerId) {
        Map<String, Object> map = new HashMap<>();
        StringBuilder stringBuilder = new StringBuilder("select sum(reward_money_real) summary, reward_money_type from de_reward ");
        stringBuilder.append("WHERE partner_id = ");
        stringBuilder.append(partnerId);
        stringBuilder.append(" group by reward_money_type");
        Query queryData = entityManager.createNativeQuery(stringBuilder.toString());
        //转换为Map集合
        queryData.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        map.put("data", queryData.getResultList());
        return map;
    }
}
